package com.yoke.controller.tongji;

import com.alibaba.druid.util.JdbcUtils;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.yoke.common.JsonMessage;
import com.yoke.common.base.NginxBaseControl;
import com.yoke.common.tools.UrlPath;
import com.yoke.controller.copy.CopyIndexControl;
import com.yoke.database.DatabaseContextHolder;
import com.yoke.system.log.LogType;
import com.yoke.system.log.SystemLog;
import com.yoke.util.DateUtil;
import com.yoke.util.StringUtil;
import org.springframework.http.MediaType;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by jiangzeyin on 2017/8/9.
 */
@Controller
@RequestMapping("tongji")
public class TongJiIndexControl extends NginxBaseControl {


    @RequestMapping(value = "index.html", produces = MediaType.TEXT_HTML_VALUE)
    public String index(String name) throws IOException {
        request.setAttribute("data", CopyIndexControl.getInfo(UrlPath.Type.None, name, getConfPath()));
        return "tongji/index";
    }

    @RequestMapping(value = "details.html", produces = MediaType.TEXT_HTML_VALUE)
    public String details(String url) throws IOException {
        return "tongji/details";
    }

    /**
     * 获取数据详情
     *
     * @param url
     * @param dateType
     * @return
     * @throws IOException
     */
    @RequestMapping(value = "data.json", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
    @ResponseBody
    public String data(String url, String dateType, String startTime, String endTime) throws IOException {
        if (StringUtil.IsEmpty(url))
            return JsonMessage.getString(100, "链接不正确");
        if (!url.startsWith("http://"))
            return JsonMessage.getString(100, "链接不正确,不是有效的http");
        String tempUrl = url.substring(url.indexOf("http://") + 7);
        int s = tempUrl.indexOf("/");
        if (s <= -1)
            return JsonMessage.getString(100, "链接不正确,-1");
        int e = tempUrl.lastIndexOf("/");
        if (s < e) {
            url = url.substring(0, e + 1 + 7);
        }
        int startTime_int = 0;
        if (!StringUtil.IsEmpty(startTime)) {
            startTime += " 00:00:00";
            startTime_int = DateUtil.formatTime(null, startTime);
            if (startTime_int <= 0)
                return JsonMessage.getString(140, "查询的开始时间不正确:" + startTime);
        }
        int endTime_int = 0;
        if (!StringUtil.IsEmpty(endTime)) {
            endTime += " 23:59:59";
            endTime_int = DateUtil.formatTime(null, endTime);
            if (endTime_int <= 0)
                return JsonMessage.getString(140, "查询的结束时间不正确：" + endTime);
            if (startTime_int == 0)
                return JsonMessage.getString(140, "没有开始时间");
            if (endTime_int <= startTime_int)
                return JsonMessage.getString(140, "结束时间大于开始事件");

        }
        // 按照时间查询
        if (startTime_int != 0 && endTime_int != 0) {
            // 判断时间范围
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date date;
            try {
                date = format.parse(startTime);
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(date);
                calendar.add(Calendar.MONTH, 3);
                String toEndMonth = DateUtil.formatTimeStamp("yyyyMM", calendar.getTime().getTime() / 1000L);
                String endMonth = endTime.split(" ")[0].replace("-", "").substring(0, 6);
                int toEndMonth_int = StringUtil.parseInt(toEndMonth, 0);
                int endMonth_int = StringUtil.parseInt(endMonth, 0);
                if (endMonth_int > toEndMonth_int)
                    return JsonMessage.getString(300, "时间范围请选择3个月之内的");
                if (endMonth_int == toEndMonth_int) {
                    String startDay = startTime.substring(8, 10);
                    String endDay = endTime.substring(8, 10);
                    if (StringUtil.parseInt(startDay) < StringUtil.parseInt(endDay))
                        return JsonMessage.getString(300, "时间范围请选择3个月之内的:-2");
                }
            } catch (Exception e1) {
                return JsonMessage.getString(140, "开始时间错误");
            }
            return doTime(url, startTime, endTime);
        }
        if (StringUtil.IsEmpty(dateType) || "7".equals(dateType))
            return doNDay(url, 7);
        if ("30".equals(dateType))
            return doNDay(url, 30);
        if ("yesterday".equals(dateType))
            return doDay(url, 1);
        if ("today".equals(dateType))
            return doDay(url, 0);
        if ("toMonth".equals(dateType))
            return toMonth(url);
        return JsonMessage.getString(300, "请稍等");
    }

    private String doTime(String url, String startTime, String endTime) {
        url = transactSQLInjection(url);
        String sql = "SELECT FROM_UNIXTIME(time, '%Y/%m/%d') as days,COUNT(1) daysSum FROM nginxAdLog where time >= UNIX_TIMESTAMP('" + startTime + "') and  time <= UNIX_TIMESTAMP('" + endTime + "') AND url LIKE '" + url + "%' GROUP BY days;";
        List<Map<String, Object>> mapList;
        try {
            mapList = executeQuery(sql);
        } catch (Exception e1) {
            SystemLog.LOG(LogType.CONTROL_ERROR).error("查询异常", e1);
            return JsonMessage.getString(300, "查询异常");
        }
        if (mapList.size() < 1)
            return JsonMessage.getString(404, "没有数据");
        Map<String, Long> map = doListMap(mapList);
        JSONArray xArray = new JSONArray();
        JSONArray yArray = new JSONArray();
        int startTimeInt = DateUtil.formatTime(null, startTime);
        int endTimeInt = DateUtil.formatTime(null, endTime);
        int day = (endTimeInt - startTimeInt) / (60 * 60 * 24);
        for (int i = 0; i <= day; i++) {
            String dateTime = "";
            try {
                dateTime = getPastDate(startTime, i);
            } catch (ParseException e) {
                SystemLog.LOG(LogType.CONTROL_ERROR).error("格式化", e);
            }
            Long value = map.get(dateTime);
            if (value == null)
                value = 0L;
            dateTime = dateTime.substring(5);
            xArray.add(dateTime);
            yArray.add(value);
        }
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("x", xArray);
        jsonObject.put("y", yArray);
        return JsonMessage.getString(200, "ok", jsonObject);
    }

    private String toMonth(String url) {
        url = transactSQLInjection(url);
        String sql = "SELECT FROM_UNIXTIME(time, '%d') as days,COUNT(1) daysSum FROM nginxAdLog where  DATE_FORMAT(NOW(), '%Y-%m') = FROM_UNIXTIME(time, '%Y-%m') AND url LIKE '" + url + "%' GROUP BY days;";
        List<Map<String, Object>> mapList;
        try {
            mapList = executeQuery(sql);
        } catch (Exception e1) {
            SystemLog.LOG(LogType.CONTROL_ERROR).error("查询异常", e1);
            return JsonMessage.getString(300, "查询异常");
        }
        if (mapList.size() < 1)
            return JsonMessage.getString(404, "没有数据");
        Map<String, Long> map = doListMap(mapList);
        JSONArray xArray = new JSONArray();
        JSONArray yArray = new JSONArray();
        String doDay_ = DateUtil.getDate("dd");
        int toDay = StringUtil.parseInt(doDay_);
        for (int i = 1; i <= toDay; i++) {
            String dateTime;
            if (i < 10)
                dateTime = "0" + i;
            else
                dateTime = i + "";
            xArray.add(dateTime);
            Long value = map.get(dateTime);
            if (value == null)
                value = 0L;
            yArray.add(value);
        }
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("x", xArray);
        jsonObject.put("y", yArray);
        return JsonMessage.getString(200, "ok", jsonObject);
    }

    private String doDay(String url, int day) {
        url = transactSQLInjection(url);
        String sql = "SELECT FROM_UNIXTIME(time, '%m/%d') as days,COUNT(1) daysSum FROM nginxAdLog where  DATE_SUB(CURDATE(), INTERVAL " + day + " DAY) = FROM_UNIXTIME(time, '%Y-%m-%d') AND url LIKE '" + url + "%' GROUP BY days;";
        List<Map<String, Object>> mapList;
        try {
            mapList = executeQuery(sql);
        } catch (Exception e1) {
            SystemLog.LOG(LogType.CONTROL_ERROR).error("查询异常", e1);
            return JsonMessage.getString(300, "查询异常");
        }
        if (mapList.size() < 1)
            return JsonMessage.getString(404, "没有数据");
        Map<String, Long> map = doListMap(mapList);
        JSONArray xArray = new JSONArray();
        JSONArray yArray = new JSONArray();
        String dateTime = getPastDate(day);
        xArray.add(dateTime);
        Long value = map.get(dateTime);
        if (value == null)
            value = 0L;
        yArray.add(value);
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("x", xArray);
        jsonObject.put("y", yArray);
        return JsonMessage.getString(200, "ok", jsonObject);
    }

    /**
     * 获取指定最近指定天数数据
     *
     * @param url
     * @param day
     * @return
     */
    private String doNDay(String url, int day) {
        url = transactSQLInjection(url);
        String sql = "SELECT FROM_UNIXTIME(time, '%m/%d') as days,COUNT(1) daysSum FROM nginxAdLog where  UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL " + day + " DAY)) <= time AND url LIKE '" + url + "%' GROUP BY days;";
        List<Map<String, Object>> mapList;
        try {
            mapList = executeQuery(sql);
        } catch (Exception e1) {
            SystemLog.LOG(LogType.CONTROL_ERROR).error("查询异常", e1);
            return JsonMessage.getString(300, "查询异常");
        }
        if (mapList.size() < 1)
            return JsonMessage.getString(404, "没有数据");
        Map<String, Long> map = doListMap(mapList);
        JSONArray xArray = new JSONArray();
        JSONArray yArray = new JSONArray();
        for (int i = day - 1; i >= 0; i--) {
            String dateTime = getPastDate(i);
            xArray.add(dateTime);
            Long value = map.get(dateTime);
            if (value == null)
                value = 0L;
            yArray.add(value);
        }
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("x", xArray);
        jsonObject.put("y", yArray);
        return JsonMessage.getString(200, "ok", jsonObject);
    }

    private static String transactSQLInjection(String str) {
        return str.replaceAll(".*([';]+|(--)+).*", " ");
        // 我认为 应该是return str.replaceAll("([';])+|(--)+","");

    }

    private static Map<String, Long> doListMap(List<Map<String, Object>> mapList) {
        Map<String, Long> map = new HashMap<>();
        mapList.forEach(item -> {
            map.put((String) item.get("days"), StringUtil.parseLong(StringUtil.convertNULL(item.get("daysSum"))));
        });
        return map;
    }

    private static List<Map<String, Object>> executeQuery(String sql) throws SQLException {
        DataSource dataSource = DatabaseContextHolder.getDataSource();
        SystemLog.LOG().info(sql);
        return JdbcUtils.executeQuery(dataSource, sql);
    }

    private static String getPastDate(int past) {
        Calendar calendar = Calendar.getInstance();
        calendar.set(Calendar.DAY_OF_YEAR, calendar.get(Calendar.DAY_OF_YEAR) - past);
        Date today = calendar.getTime();
        SimpleDateFormat format = new SimpleDateFormat("MM/dd");
        return format.format(today);
    }

    private static String getPastDate(String time, int past) throws ParseException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = format.parse(time);
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        calendar.set(Calendar.DAY_OF_YEAR, calendar.get(Calendar.DAY_OF_YEAR) + past);
        Date today = calendar.getTime();
        format = new SimpleDateFormat("yyyy/MM/dd");
        return format.format(today);
    }
}
